# -*- coding: utf-8 -*-
import pandas as pd
import pymongo
import matplotlib.pyplot as plt
import datetime
import Core.Gadget as Gadget
import Core.DataSeries as DataSeries
import Core.IO as IO
import numpy as np
import random
import math
import copy

ANNULIZED_FACTOR = 245
MONTHLY_FACTOR = 20
QUARTERLY_FACTOR = 60

#
def PrintFactor(database, symbol, factorName, filter={}):
    values = IO.LoadFactor2(database, symbol, factorName, filter)
    for pair in values:
        print(str(pair["StdDateTime"]) + " " + str(pair["Value"]))


def LoadDataFromTextFile(symbol):
    kkwood = 1


#定义生成权重的函数
def HalflifeWeight(se,h_life=60.0):
    a = np.arange(se.shape[0]).reshape(-1, 1)
    b = pow(0.5, (se.shape[0] - a - 1) / h_life)
    return b


#---Build Factor Database Structure---
def BuildFactorDatabase(database,instruments):
    now = datetime.datetime.now()
    now = Gadget.ToUTCDateTime(now)
    for instrument in instruments:
        symbol = instrument["Symbol"]
        database.insert("Factor",symbol,{"Name":"Test","StdDateTime":now})
        database.creatIndex("Factor",symbol,"Name")
        database.creatIndex("Factor",symbol,"StdDateTime")


def CalcRanking2(database, factorName, datetime1, datetime2, market=""):

    filter = {}
    if market != "":
        filter = {"Market": market}

    #
    instruments = database.find("Instruments", "Stock", query=filter)

    #
    dfData = IO.LoadMultiInstrumentsFactorsAsDataFrame(database, factorName, datetime1, datetime2, instruments)
    monthEndDates = Gadget.GenerateEndDayofMonth(datetime1,datetime2)
    for monthEndDate in monthEndDates:
        dfSmallData = dfData[dfData["DateTime"] <= monthEndDate]

        if len(dfTemp) == 0:
            continue

        dfTemp = dfSmallData.iloc[-1, :]

        #---Row Sorting---


        #---Ranking %---


        #---ZScore---
        # zScore = (value - mean) / stdDev

        #---SaveToDataBase---


# --- Market Industry All---
def CalcRanking(database, instrumentByIndustry, factorName, datetime1, datetime2):
    #---Load DataSeries into Cache---
    dataSeriesBySymbol = {}
    for instrument in instrumentByIndustry:
        symbol = instrument["Symbol"]
    #for symbol in list:
        filter = {}
        filter["StdDateTime"] =  {"$gt": datetime1, "$lt": datetime2}
        filter["Name"] = factorName
        sort = [("StdDateTime",pymongo.ASCENDING)]
        factors = database.findWithFilter("Factor",symbol,filter, sort)
        dataSeriesBySymbol[symbol] = factors

    #---Generate Factor Value only when month change---
    #---换月时，为该因子添加数值---
    lastDateTime = datetime1
    nowDateTime = datetime1
    while nowDateTime <= datetime2:
        nowDateTime = nowDateTime + datetime.timedelta(days = 1)
        #---Month Changed---
        if nowDateTime.month != lastDateTime.month:
            elements = []
            #---对其时间---
            for k,v in dataSeriesBySymbol.items():
                entry = Gadget.Find(v,nowDateTime,"Previous")
                if entry != None:
                    elements.append(entry)

            length = len(elements)
            if length > 0:
                print("Insert Ranking of[" + factorName + "] for " + str(length) + " Instruments @ " + str(nowDateTime))
                values = []
                elements.sort(key=lambda obj:obj.get('Value'), reverse = True)
                for element in elements:
                    values.append(element["Value"])
                stdDev = np.std(values)
                mean = np.mean(values)

                for i in range(length):
                    element = elements[i]
                    symbol = element["Symbol"]
                    value = element["Value"]
                    zScore = (value - mean) / stdDev
                    rank = i + 1
                    surpassIncludeSelf = length - rank + 1
                    ranking = surpassIncludeSelf / length
                    WriteFactorToDataBase(symbol, factorName + "ZScore",nowDateTime,zScore)
                    WriteFactorToDataBase(symbol, factorName + "Ranking",nowDateTime,ranking)

        lastDateTime = nowDateTime


def Industries(instruments):
    list = {}
    for instrument in instruments:
        if "Properties" in instrument and "SWIndustry1" in instrument["Properties"]:
            industry = instrument["Properties"]["SWIndustry1"]
            if industry not in list:
                list[industry] = []
            list[industry].append(instrument)
    return list


def FindMostSuitableSeasonalReportAlignDate(database):
    datetime1 = datetime.datetime(2005,1,1)
    datetime2 = datetime.datetime(2016,12,31)

    table = {}
    reportDates = GenerateReportDates(datetime1,datetime2)
    for reportDate in reportDates:
        reportDate = Gadget.ToDateTimeString(reportDate)
        table[reportDate] = {"Count":0,"Last":"","LateThanWeThought":0}

    count = 0
    instruments = database.findWithFilter("Instruments","Stock")
    for instrument in instruments:
        symbol = instrument["Symbol"]
        count = count + 1
        print(symbol + " " + str(count))
        statements = database.findWithFilter("Fundamental", symbol + "_Fundamental",{})
        for statement in statements:
            reportDate = statement["ReportDate"]
            reportDateTime = Gadget.ParseDateTime(reportDate)
            releaseDate = statement["ReleaseDate"]
            releaseDateTime = Gadget.ParseDateTime(releaseDate)
            period = statement["Period"]
            releaseDate1 = datetime.datetime(reportDateTime.year,5,1)#一季报理论时间
            releaseDate2 = datetime.datetime(reportDateTime.year,9,1)#二季报理论时间
            releaseDate3 = datetime.datetime(reportDateTime.year,12,1)#三季报理论时间
            releaseDate4 = datetime.datetime(reportDateTime.year + 1,5,1)#四季报理论时间


            if table.get(reportDate) == None:
                print("Error Report Date:" + reportDate)
            else:
                #---
                statistics = table[reportDate]
                statistics["Count"] = statistics["Count"] + 1
                #---
                if statistics["Last"] == "":
                    statistics["Last"] = releaseDateTime
                elif releaseDateTime > statistics["Last"]:
                    statistics["Last"] = releaseDateTime
                #---
                if period == 1 and releaseDateTime > releaseDate1:
                    statistics["LateThanWeThought"] = statistics["LateThanWeThought"] + 1
                if period == 2 and releaseDateTime > releaseDate2:
                    statistics["LateThanWeThought"] = statistics["LateThanWeThought"] + 1
                if period == 3 and releaseDateTime > releaseDate3:
                    statistics["LateThanWeThought"] = statistics["LateThanWeThought"] + 1
                if period == 4 and releaseDateTime > releaseDate4:
                    statistics["LateThanWeThought"] = statistics["LateThanWeThought"] + 1

    print(table)
    for k,v in table.items():
        print(k + " " + str(v))


def DeleteFactor(database, factorName):
    database.delete("Factor", factorName,{})


def SaveCSVAsFactor(database, pathFileName):
    pass


def CalculateTTM(database, instrument, datetime1, datetime2, field, fundamentalsBySymbol={}):
    #
    symbol = instrument["Symbol"]
    #
    # ---Create DataSeries (Only Tempo DataSeries)---
    # --- TTM Rolling ---
    dataSeries_TTM = DataSeries.DataSeries(symbol + "_" + field + "TTM")

    #
    filter = {}
    sort = [("StdReportDate", pymongo.ASCENDING)]
    # ---读取全部Statements---
    if symbol not in fundamentalsBySymbol:
        # statements = database.findWithFilter("Fundamental", symbol + "_Fundamental", filter, sort)
        statements = database.getDataSeries(symbol + "_Fundamental")
        fundamentalsBySymbol[symbol] = statements
    statements = fundamentalsBySymbol[symbol]

    # ---DateTime Range---
    minimalDT = datetime.datetime(1980, 1, 1)
    minimalDT = Gadget.ToUTCDateTime(minimalDT)

    # --- Loop Statements ---
    for statement in statements.items:
        # print("### " + str(statement["DateTime"]) + " ###")
        #
        ttmDependentStatementReady = True
        completeToCalcTTM = False
        #
        # lastStatement = statements.Get(dt, "Previous")  # Last File
        lastStatement = statement

        #
        reportDate = lastStatement["ReportDate"]
        releaseDate = lastStatement["ReleaseDate"]
        reportDT = lastStatement["StdReportDate"]
        releaseDT = lastStatement["StdDateTime"]
        #
        if releaseDT < minimalDT:
            print(symbol + " ReleaseDate Error, May no Statement Exist @BacktestDate " + str(dt))
            continue
        #

        lastReleaseYear = releaseDT.year

        # --- 关键事件点，报告期---
        # --- Fix Special Case, Release Data & Report Data 差距达到两年（000620.SZ） ---
        # --- 从所能得到的最近一次报告往前推 ---
        lastYearDateTime1 = datetime.datetime(lastReleaseYear - 1, 3, 31)  # 去年一季报 基于 current backtest date
        lastYearDateTime2 = datetime.datetime(lastReleaseYear - 1, 6, 30)  # 去年半年报
        lastYearDateTime3 = datetime.datetime(lastReleaseYear - 1, 9, 30)  # 去年三季报
        lastYearDateTime4 = datetime.datetime(lastReleaseYear - 1, 12, 31)  # 去年年报
        lastYearDateTime1 = Gadget.ToUTCDateTime(lastYearDateTime1)
        lastYearDateTime2 = Gadget.ToUTCDateTime(lastYearDateTime2)
        lastYearDateTime3 = Gadget.ToUTCDateTime(lastYearDateTime3)
        lastYearDateTime4 = Gadget.ToUTCDateTime(lastYearDateTime4)

        # --- 已经通过 lastReleaseYear 确定了需要知道的“去年” ---
        # --- 通过报告期，不是发布日期，找到准确的报告---
        lastYearStatement1 = Gadget.Find(statements.items, lastYearDateTime1, "Exact",
                                         datetimeField="StdReportDate")  # 去年一季报
        lastYearStatement2 = Gadget.Find(statements.items, lastYearDateTime2, "Exact",
                                         datetimeField="StdReportDate")  # 去年半年报
        lastYearStatement3 = Gadget.Find(statements.items, lastYearDateTime3, "Exact",
                                         datetimeField="StdReportDate")  # 去年三季报
        lastYearStatement4 = Gadget.Find(statements.items, lastYearDateTime4, "Exact",
                                         datetimeField="StdReportDate")  # 去年年报

        # ---Cache to Check Null Values---
        previousStatements = []
        previousStatements.append(lastYearStatement1)
        previousStatements.append(lastYearStatement2)
        previousStatements.append(lastYearStatement3)
        previousStatements.append(lastYearStatement4)

        #
        document = {}
        document["Symbol"] = symbol
        document["StdDateTime"] = lastStatement["StdDateTime"]

        #
        try:

            # --- Check Dependent Statement ---
            if "Values" not in lastStatement or field not in lastStatement["Values"]:
                ttmDependentStatementReady = False

            # ---Calc TTM---
            valueTTM = 0

            # --- Check Dependent Statement ---
            if lastStatement["Period"] == 1 or lastStatement["Period"] == 2 or lastStatement["Period"] == 3:
                period = lastStatement["Period"]
                index = period - 1
                #
                if lastYearStatement4 is None or "Values" not in lastYearStatement4:
                    ttmDependentStatementReady = False
                #
                elif previousStatements[index] is None or "Values" not in previousStatements[index]:
                    ttmDependentStatementReady = False
                #
                elif field not in previousStatements[index]["Values"]:
                    ttmDependentStatementReady = False
                #
                elif field not in lastYearStatement4["Values"]:
                    ttmDependentStatementReady = False

            # --- Calculation for TTM ---
            if ttmDependentStatementReady:
                #
                if lastStatement["Period"] == 1:
                    # 2-3-4-1
                    valueTTM = lastStatement["Values"][field] + \
                                                      lastYearStatement4["Values"][field] - \
                                                      lastYearStatement1["Values"][field]
                #
                elif lastStatement["Period"] == 2:
                    # 3-4-1-2
                    #
                    valueTTM = lastStatement["Values"][field] + \
                                                      lastYearStatement4["Values"][field] - \
                                                      lastYearStatement2["Values"][field]
                #
                elif lastStatement["Period"] == 3:
                    # 4-1-2-3
                    #
                    valueTTM = lastStatement["Values"][field] + \
                                                      lastYearStatement4["Values"][field] - \
                                                      lastYearStatement3["Values"][field]
                #
                elif lastStatement["Period"] == 4:
                    # 1-2-3-4
                    valueTTM = lastStatement["Values"][field]

                #
                else:
                    print(symbol + " CalcValuatioFactor::UnknownReportPeriod @ReportDate " + reportDate)
                    ttmDependentStatementReady = False

            # --- Calculation for TTM ---
            if ttmDependentStatementReady:
                document["Value"] = valueTTM
                completeToCalcTTM = True
            else:
                print(
                    symbol + " CalcValuatioFactor::Can't Calc TTM Factor, Missing Some Previous Dependent Statements " + " @BacktestDate " + str(
                        statement["DateTime"]) + " @ReportDate " + reportDate)

        except ZeroDivisionError as e:
            print(symbol + " CalcValuatioFactor::Calculation ZeroDivisionError " + str(e) + " @BacktestDate " + str(
                statement["DateTime"]) + " @ReportDate " + reportDate)
        except KeyError as e:
            print(symbol + " CalcValuatioFactor::Calculation KeyError " + str(e) + " @BacktestDate " + str(
                statement["DateTime"]) + " @ReportDate " + reportDate)
        except Exception as e:
            print(symbol + " CalcValuatioFactor::Calculation Serious Error " + " @BacktestDate " + str(
                statement["DateTime"]) + " @ReportDate " + reportDate)
            print(e)
            kkwood = 0

        # --- Add document to DataSeries ---
        if completeToCalcTTM:
            dataSeries_TTM.Add(document)

    # ---Loop Statements---
    return dataSeries_TTM


def CalculateTTM2(database, dfFundamental, field):

    # --- 准备一个临时DF，以ReportDate为Index---
    dfTemp = dfFundamental.set_index(['ReportDate'])
    # print(dfFundamental.head())

    # --- 关键事件点，报告期---
    # --- Fix Special Case, Release Data & Report Data 差距达到两年（000620.SZ） ---
    # --- 从所能得到的最近一次报告往前推 ---
    #
    dfFundamental[field + "_TTM"] = np.nan

    # Sort by ReportDate 1-2-3-4 ...
    # dfFundamental.sort_values["ReportDate"] # 应该已经按照ReportDate排好序
    for index in dfFundamental.index:
        #
        period = dfFundamental.at[index, "Period"]
        value = dfFundamental.at[index, field]
        if value == None:
            value = np.nan

        #
        reportDate = dfFundamental.at[index, "ReportDate"]
        reportYear = reportDate.year

        #
        lastYearReportDates = []
        lastYearReportDates.append(datetime.date(reportYear - 1, 3, 31)) # 去年一季报
        lastYearReportDates.append(datetime.date(reportYear - 1, 6, 30))  # 去年半年报
        lastYearReportDates.append(datetime.date(reportYear - 1, 9, 30))  # 去年三季报
        lastYearReportDates.append(datetime.date(reportYear - 1, 12, 31))  # 去年年报

        # --- Get Row : where is a period Report ---
        #lastYear1 = dfFundamental.loc[dfFundamental['ReportDate'] == lastYearReportDate1, field]
        #lastYear2 = dfFundamental.loc[dfFundamental['ReportDate'] == lastYearReportDate2, field]
        #lastYear3 = dfFundamental.loc[dfFundamental['ReportDate'] == lastYearReportDate3, field]
        #lastYear4 = dfFundamental.loc[dfFundamental['ReportDate'] == lastYearReportDate4, field]

        # --- Check if existed---
        lastYearValues = []
        for i in range(4):
            lastYearValues.append(np.nan)
            if lastYearReportDates[i] in dfTemp.index:
                lastYearValues[i] = dfTemp.loc[lastYearReportDates[i], field]

        # --- Different Period---
        valueTTM = np.nan
        if period == 1:
            # 2-3-4-1
            valueTTM = value + lastYearValues[3] - lastYearValues[0]
        #
        elif period == 2:
            # 3-4-1-2
            valueTTM = value + lastYearValues[3] - lastYearValues[1]
        #
        elif period == 3:
            # 4-1-2-3
            valueTTM = value + lastYearValues[3] - lastYearValues[2]
        #
        elif period == 4:
            # 1-2-3-4
            valueTTM = value
        #
        else:
            print(" CalcValuatioFactor::UnknownReportPeriod @ReportDate " + reportDate)

        #
        dfFundamental.at[index, field + "_TTM"] = valueTTM

    # ---Fill Nan Value---
    # dfFundamental.fillna(method='ffill',inplace=True)
    # print(dfFundamental.head(10))

    # ---重构 数字index, 挪至外部 CalcFundamentalFactor---
    # dfFundamental.reset_index(drop=True, inplace=True)
    # dfFundamental.rename(columns={'ReportDate1': "ReportDate"}, inplace=True)  # 恢复 ReportDate
    pass


def CalculateLYR(database, instrument, datetime1, datetime2, field, fundamentalsBySymbol={}):
    #
    symbol = instrument["Symbol"]
    #
    # ---Create DataSeries (Only Tempo DataSeries)---
    # --- TTM Rolling ---
    dataSeries_LYR = DataSeries.DataSeries(symbol + "_" + field + "LYR")

    #
    filter = {}
    sort = [("StdReportDate", pymongo.ASCENDING)]
    # ---读取全部Statements---
    if symbol not in fundamentalsBySymbol:
        # statements = database.findWithFilter("Fundamental", symbol + "_Fundamental", filter, sort)
        statements = database.getDataSeries(symbol + "_Fundamental")
        fundamentalsBySymbol[symbol] = statements
    statements = fundamentalsBySymbol[symbol]

    # ---DateTime Range---
    minimalDT = datetime.datetime(1980, 1, 1)
    minimalDT = Gadget.ToUTCDateTime(minimalDT)

    # ---整理出所有的年报---
    # --- 没有尝试解决 statements 顺序混乱的情况 ---
    annualStatements = []
    for statement in statements.items:
        lastStatementReady = True
        completeCalcLYR = False

        # --- 只处理年报 ---
        if statement["Period"] == 4:
            # annualStatements.append(statement)

            # --- Check Dependent Statement ---
            if "Values" not in statement or field not in statement["Values"]:
                lastStatementReady = False

            #
            if lastStatementReady:
                document = {}
                document["Symbol"] = symbol
                document["StdDateTime"] = statement["StdDateTime"]
                document["Value"] = statement["Values"][field]
                #
                completeCalcLYR = True

            if completeCalcLYR:
                # --- Add document to DataSeries ---
                dataSeries_LYR.Add(document)

    # ---Loop Statements---

    # --- Loop Statements ---
    # --- 年报顺序有可能会乱，确保找到了正确的“最近的去年”
    #for statement in annualStatements:
    #    #
    #    releaseDT = statement["StdDateTime"]
    #    lastReleaseYear = releaseDT.year
    #    lastYearDateTime4 = datetime.datetime(lastReleaseYear - 1, 12, 31)  # 去年年报
    #    #
    #    lastYearStatement4 = Gadget.Find(statements.items, lastYearDateTime4, "Exact",
    #                                     datetimeField="StdReportDate")  # 去年年报

    return dataSeries_LYR


def CalculateLYR2(database, dfFundamental, field):
    #
    dfFundamental[field + "_LYR"] = np.nan
    for index in dfFundamental.index:
        # print(dfFundamental.at[index, "ReportDate"])
        if dfFundamental.at[index, "Period"] == 4:
            dfFundamental.at[index, field + "_LYR"] = dfFundamental.at[index, field]
    #
    # dfFundamental.fillna(method='ffill',inplace=True)


def CalculateLF(database, instrument, datetime1, datetime2, field, fundamentalsBySymbol={}):
    #
    symbol = instrument["Symbol"]
    #
    # ---Create DataSeries (Only Tempo DataSeries)---
    # --- TTM Rolling ---
    dataSeries_LF = DataSeries.DataSeries(symbol + "_" + field + "LF")

    #
    filter = {}
    sort = [("StdReportDate", pymongo.ASCENDING)]
    # ---读取全部Statements---
    if symbol not in fundamentalsBySymbol:
        # statements = database.findWithFilter("Fundamental", symbol + "_Fundamental", filter, sort)
        statements = database.getDataSeries(symbol + "_Fundamental")
        fundamentalsBySymbol[symbol] = statements
    statements = fundamentalsBySymbol[symbol]

    # ---DateTime Range---
    minimalDT = datetime.datetime(1980, 1, 1)
    minimalDT = Gadget.ToUTCDateTime(minimalDT)

    # --- Loop Statements ---
    # --- 没有尝试解决 statements 顺序混乱的情况 ---
    for statement in statements.items:
        # print("### " + str(statement["DateTime"]) + " ###")
        #
        lastStatementReady = True
        completeCalcLF = False
        #
        # lastStatement = statements.Get(dt, "Previous")  # Last File
        lastStatement = statement
        #
        releaseDT = lastStatement["StdDateTime"]
        if releaseDT < minimalDT:
            print(symbol + " ReleaseDate Error, May no Statement Exist @ReportDate " + str(statement["ReportDate"]))
            continue

        #
        document = {}
        document["Symbol"] = symbol
        document["StdDateTime"] = lastStatement["StdDateTime"]

        # --- Check Dependent Statement ---
        if "Values" not in lastStatement or field not in lastStatement["Values"]:
            lastStatementReady = False

        if lastStatementReady:
            document["Value"] = lastStatement["Values"][field]
            #
            completeCalcLF = True

        if completeCalcLF:
            # --- Add document to DataSeries ---
            dataSeries_LF.Add(document)

    # --- Loop Statements ---
    return dataSeries_LF


def PrintProfileFactor(database, datetime2, factorNames, filter={}):
    #
    outputFolder = "D:/Data/ProfileFactor/"
    #
    #filter = {}
    #filter = {"Market": "创业板"}
    #
    instruments = database.find("Instruments", "Stock", query=filter)

    listedInstruments = Gadget.FindListedInstrument(database, datetime2)
    index = []
    for i in range(len(listedInstruments)):
        index.append(i)
    random.shuffle(index)

    #
    filteredInstruments = []
    for instrument in instruments:
        for i in index:
            if instrument["Symbol"] == listedInstruments[i]["Symbol"]:
                filteredInstruments.append(instrument)
                break

    #instruments2 = []
    #for instrument in instruments:
    #    instruments2.append(instrument)
    #    if len(instruments2) >= 10:
    #        break
    #instruments = instruments2

    #
    instruments = filteredInstruments
    dfData = IO.LoadFactorsProfileAsDataFrame(database, datetime2, instruments, factorNames)
    #print(dfData)
    #max = dfData[factorName].max()
    #min = dfData[factorName].max()
    if "Market" not in filter:
        filter["Market"] = "All"
    dfData.to_csv(outputFolder + filter["Market"] + "_ProfileFactor_" + Gadget.ToDateString(datetime2) + ".csv")


def PortfolioReturn(database, instruments, datetime1, datetime2, name=""):

    outputFolder = "D:/Data/FamaFrench3-4/"
    dateStr1 = Gadget.ToDateString(datetime1)
    dateStr2 = Gadget.ToDateString(datetime2)

    # ---Test---
    #simpleInstruments = []
    #for i in range(3):
    #   simpleInstruments.append(instruments[i])
    #instruments = simpleInstruments

    # ---Step1 Load BarSeries BClose Price---
    dfPrice = IO.LoadMultiInstrumentsBarsAsDataFrame(database, datetime1, datetime2, instruments)
    # dfPrice.to_csv(outputFolder + "dfPrice_" + name + "_" + dateStr1 + "-" + dateStr2 + ".csv")

    # ---Read Data From CSV---
    #dfPrice = pd.DataFrame()
    #dfPrice = dfPrice.from_csv(outputFolder + "dfPrice_" + name + "_" + dateStr1 + "-" + dateStr2 + ".csv")
    #dfPrice["DateTime"] = pd.to_datetime(dfPrice["DateTime"])

    # print(dfPrice)

    # ---Step2 Calc Return ---
    dfReturn = pd.DataFrame()
    dfReturn["DateTime"] = dfPrice['DateTime']

    # dfPrice 读取的时候，如果没数据就不计入了
    symbols = dfPrice.columns.values
    for symbol in symbols:
        if symbol == "DateTime":
            continue
        # dfReturn[symbol] = math.log(dfPrice[symbol] / dfPrice[symbol].shift(1) , e)
        dfReturn[symbol] = dfPrice[symbol] / dfPrice[symbol].shift(1) - 1
    #
    dfReturn.to_csv(outputFolder + "dfReturn_" + name + "_" + dateStr1 + "-" + dateStr2 + ".csv")

    # ---Step3 Average to Get Portfolio Return---
    #Average(dfReturn)
    # print(dfPrice)
    for i in range(len(dfReturn)):
        countSymbols = 0
        average = 0
        for j in range(len(symbols)):
            symbol = symbols[j]
            if symbol == "DateTime":
                continue
            value = dfReturn.at[i, symbol]
            if math.isnan(value):
                kkwood = 0
            else:
                average += value
                countSymbols += 1
        if countSymbols == 0:
            average = np.nan
        else:
            average /= countSymbols
        dfReturn.at[i, 'Average'] = average
        #dfReturn.at[i,'Average'] = dfPrice.iloc[i,countSymbols:].mean()


    #print(" Price ")
    #print(dfPrice)

    # Take DateTime as Index
    # dfPortfolio = pd.DataFrame(index=dfPrice.index)
    # dfPortfolio["Average"] = dfPrice["Average"]

    # Not Take Datetime as Index
    dfPortfolio = pd.DataFrame()
    dfPortfolio[["DateTime", "Average"]] = dfReturn[["DateTime", "Average"]]

    #
    #print(" Portfolio ")
    #print(dfPortfolio)

    return dfPortfolio


def DeterminFactorCalculatingRange(database, instrument, datetime1, datetime2, factorName, update):

    #
    # instrumentDateTime1 = Gadget.ToLocalDateTime(instrument["DateTime1"]).date()
    # instrumentDateTime2 = Gadget.ToLocalDateTime(instrument["DateTime2"]).date()
    instrumentDateTime1 = instrument["DateTime1"].date()
    instrumentDateTime2 = instrument["DateTime2"].date()
    #
    datetime1 = datetime1.date()
    datetime2 = datetime2.date()

    if update:
        #
        # factors = IO.LoadFactorsAsDataFrame(database, instrument["Symbol"], factors=[factorName])
        factors = database.Find("Factor", factorName, filter={"Symbol": instrument["Symbol"]}, sort=[("DateTime", -1)])
        #
        #print(factors.head())

        # datetime1
        if len(factors) == 0: # cold start
            realDateTime1 = instrumentDateTime1
        else:
            realDateTime1 = factors[0]["DateTime"]
            realDateTime1 = Gadget.ToLocalDateTime(realDateTime1).date()
            # 数据最后一天顺延一天
            realDateTime1 = realDateTime1 + datetime.timedelta(days=1)

        # datetime2
        realDateTime2 = datetime2
        pass
    #
    else:
        realDateTime1 = datetime1
        # realDateTime2 = datetime2 + datetime.timedelta(days=1)
        realDateTime2 = datetime2

    #
    # realDateTime1 = Gadget.ToUTCDateTime(realDateTime1)
    # if realDateTime2.tzinfo == None:
    #    realDateTime2 = Gadget.ToUTCDateTime(datetime2)

    # IPO Date
    if instrumentDateTime1 > realDateTime1:
        realDateTime1 = instrumentDateTime1

    # Delisted Date
    if instrumentDateTime2 < realDateTime2:
        realDateTime2 = instrumentDateTime2 #  严格来说更新至退市前一天

    # 在日期上相等可以认为相等
    if realDateTime1 >= realDateTime2:
        realDateTime1 = realDateTime2
    else:
        # 移动至第二天0点，以包括最后一天
        realDateTime2 = realDateTime2 + datetime.timedelta(days=1)

    # 保证最后的返回值是DateTime（0点基础）
    realDateTime1 = Gadget.DateToDateTime(realDateTime1)
    realDateTime2 = Gadget.DateToDateTime(realDateTime2)

    #
    return realDateTime1, realDateTime2


def GenarateDocument(symbol, localDateTime, value, createKey=False):
    document = {}
    document["Symbol"] = symbol
    document["Value"] = value

    # local datetime
    document["UTCDateTime"] = Gadget.ToUTCDateTime(localDateTime)
    document["DateTime"] = localDateTime
    document["Date"] = localDateTime.date()

    # localDateTime = Gadget.ToLocalDateTime(utcDatetime)
    # localDateTime = Gadget.DeleteTimeZone(localDateTime) # 必须删除TimeZone
    # document["DataSeries"] = dailyFactors.name
    if createKey:
        document["Key"] = symbol + "_" + Gadget.ToDateString(document["DateTime"])

    return document


# PriceBased or ReturnBased
def CalcDailyFactor(database, instruments, datetime1, datetime2, factorName, callback, update=False, params={},
                    fields=[], startIndex=0):
    #
    bmInstrument = database.Find("Instruments", "Index", {"Symbol": "000001.SH"})
    bmInstrument = bmInstrument[0]

    #
    folderName = "d:/data/CalcDailyFactor/" + factorName
    Gadget.CreateFolder(folderName)

    #
    # datetime1 = Gadget.ToUTCDateTime(datetime1)
    # datetime2 = Gadget.ToUTCDateTime(datetime2)

    #
    count = 0
    for instrument in instruments:
        count = count + 1
        symbol = instrument["Symbol"]

        #
        print("")
        print("Calculating: " + factorName + " " + symbol + " length:" + " count:" + str(count) + " @Time " + str(
            datetime.datetime.now()))

        #
        if count < startIndex:
            continue

        # if symbol != "000786.SZ":
        #    continue

        # ---创建容器---
        dailyFactors = DataSeries.DataSeries(symbol + "_" + factorName + "_Factor")

        # ---确定起始日期---
        realDateTime1, realDateTime2 = DeterminFactorCalculatingRange(database, instrument, datetime1, datetime2,
                                                                      factorName, update)
        #
        if realDateTime1 == realDateTime2:
            print("Unnecessary to Update", symbol)
            continue

        # 为了避免周末和节假日，Daily指标，特意回溯7天
        period = params.get("Period")
        if period == None:
            maxReference = 7
        elif period == ANNULIZED_FACTOR:
            maxReference = 366
        elif period == MONTHLY_FACTOR:
            maxReference = 31
        elif period == QUARTERLY_FACTOR:
            maxReference = 93
        else:
            maxReference = int(period * 1.4)
        #
        referenceDateTime1 = realDateTime1 - datetime.timedelta(days=maxReference)
        if referenceDateTime1 < datetime.datetime(1950, 1, 1):
            continue

        # Convert to UTC DateTime
        # referenceDateTime1 = Gadget.ToUTCDateTime(referenceDateTime1)
        # realDateTime1 = Gadget.ToUTCDateTime(realDateTime1)
        # realDateTime2 = Gadget.ToUTCDateTime(realDateTime2)

        #
        # if "InstrumentType" not in instrument:
        #    continue
        instrumentType = instrument["InstrumentType"]
        if instrumentType == "Stock":
            defaultFields = ["BClose", "BOpen", "BHigh", "BLow"]
            defaultFields.extend(fields)
            dfBars = IO.LoadBarsAsDataFrame(database, symbol, instrumentType=instrumentType,
                                            datetime1=referenceDateTime1, datetime2=realDateTime2, fields=defaultFields)

        # Might be Index
        elif instrumentType == "Index":
            defaultFields = ["Close", "Open", "High", "Low"]
            defaultFields.extend(fields)
            dfBars = IO.LoadBarsAsDataFrame(database, symbol, instrumentType=instrumentType,
                                            datetime1=referenceDateTime1, datetime2=realDateTime2, fields=defaultFields)
            dfBars.rename(columns={'Close': "BClose",'Open': "BOpen",'High': "BHigh",'Low': "BLow"}, inplace=True)

        # neither Stock or Index
        if len(dfBars) == 0:
            #return pd.DataFrame()
            print(symbol + " NoData in Range")
            continue

        # print(dfBars.head())
        rangeBegin = dfBars.loc[0, "DateTime"]
        rangeEnd = dfBars.loc[len(dfBars) - 1, "DateTime"]
        #
        dfBenchmark = IO.LoadBarsAsDataFrame(database, bmInstrument["Symbol"], instrumentType="Index",
                                             datetime1=rangeBegin, datetime2=rangeEnd, fields=[])
        # print(dfBenchmark.head())

        dfData = pd.merge(dfBenchmark, dfBars, on='DateTime', how='left')
        dfData.fillna(method='pad', inplace=True)
        # print(dfData.head())

        #
        dfData.to_csv(folderName + "/" + symbol + ".csv")

        #
        callback(dfData, factorName, params=params)
        if len(dfData) == 0:
            print("CalcDailyFactor " + symbol + " No BarData")
            continue

        # print(dfData.head())
        dfData.to_csv(folderName + "/" + symbol + ".csv")

        # ---Loop DataFrame---
        for index, row in dfData.iterrows():
            #
            curDateTime = row["DateTime"]
            curDateTime = pd.Timestamp.to_pydatetime(curDateTime)

            if curDateTime < realDateTime1:
                continue

            value = row[factorName]

            # 只提示，不处理
            if math.isnan(row[factorName]):
                # print(factorName + " is NaN " + symbol + " @ " + str(curDateTime))
                continue

            # if index == 0 and math.isnan(value):
            #    continue

            document = GenarateDocument(symbol, curDateTime, value)
            document["Key"] = symbol + "_" + Gadget.ToDateTimeString(document["DateTime"])

            #
            dailyFactors.Add(document)

        # ---Write to Database---
        database.SaveDataSeries(dailyFactors, dataType="Factor")
        print("Calculated: " + factorName + " " + symbol + " length:" + " count:" + str(count) + " @Time " + str(
            datetime.datetime.now()))
        pass


# ---Quarterly Field---> Automaticaly Calc TTM, LYR---
# ---Other Field ---> LYR
def CalcFundamentalFactor(database, instruments, datetime1, datetime2,
                          factorName, callback, fields=[], quarterlyFields=[],
                          update=False, maxReferenceYear=1, params={}, outputField=[],
                          startIndex=0, test=False, export=False):

    #
    outputFolder = "D:/Data/CalcFundamentalFactor/" + factorName + "/"
    Gadget.CreateFolder(outputFolder)

    #
    # ---用于确定，交易日+交易时间，写入数据时间戳---
    bmQuotes = database.GetDataSeries("000001.SH", dataType="DailyBar",
                                      datetime1=datetime1, datetime2=datetime2,
                                      instrumentType="Index")

    #
    count = 0
    for instrument in instruments:
        symbol = instrument["Symbol"]
        count += 1

        # ---算法进行测试时，只计算一个Instrument---
        if test and count > 1:
            break

        if count < startIndex:
            continue

        #if symbol != "000001.SZ":
        #  continue

        print("")
        print(datetime.datetime.now(), "Calculating:", factorName, symbol, "Index", count)

        # ---Rebuild DateTime Range ---
        # ---确定起始日期---
        realDateTime1, realDateTime2 = DeterminFactorCalculatingRange(database, instrument, datetime1, datetime2,
                                                                     factorName, update)
        #
        print(datetime.datetime.now(), "TimeRange", realDateTime1, realDateTime2)

        if realDateTime1 == realDateTime2:
            print("Unnecessary Update / Delisted or Updated " + symbol)
            continue

        # ---Create DataSeries---
        dataSeries = DataSeries.DataSeries(symbol + "_" + factorName + "_Factor")

        # ---补齐与合并要提取的字段---
        totalFields = copy.deepcopy(fields)
        for field in quarterlyFields:
            if field not in totalFields:
                totalFields.append(field)

        # ---依赖至少 X 年数据---
        referenceDateTime1 = realDateTime1 + datetime.timedelta(days=-1 * maxReferenceYear * 365)

        # ---可以以referenceDateTime1为起点，但是在目前负载不大的情况下，建议取全部的Fundamental---
        dfFundamental = IO.LoadFundamentalsAsDataFrame(database, symbol, datetime1=None, datetime2=realDateTime2,
                                                       fields=totalFields)
        print(datetime.datetime.now(), "Loaded Fundamental Data")

        #
        if len(dfFundamental) == 0:
            print("CalcFundamentalFactor " + symbol + " NoData")
            continue

        # Nan => 0
        # for field in totalFields:
        #    dfFundamental.
        dfFundamental.fillna(0, inplace=True)

        # ---排序，防止错误---
        # 排序已在 Load Database 中完成，可以省略
        # dfFundamental.sort_values(by="ReportDate", ascending=True, inplace=True)
        # dfFundamental.reset_index(drop=True, inplace=True)
        # print(dfFundamental.head(10))
        # dfFundamental.to_csv(outputFolder + "dfFundamental_" + factorName + "_" + symbol + ".csv")

        # ---Calculate TTM / LYR---
        # ---LYR for All item---
        for field in totalFields:
            CalculateLYR2(database, dfFundamental, field)
        # print(dfFundamental.head(30))

        # ---TTM only for Quarterly item ---
        for field in quarterlyFields:
            CalculateTTM2(database, dfFundamental, field)
        # print(dfFundamental.head(10))

        # 这一步可能会出现Bug，会把一些缺失的报表用前值填补
        # 从而导致LYR TTM不准确
        dfFundamental.fillna(method='ffill', inplace=True)

        # ---output---
        #print(dfFundamental.head(10))
        # dfFundamental.to_csv(outputFolder + "dfFundamental_" + factorName + "_" + symbol + ".csv")

        # ---Calculation Logics---
        params["Fields"] = fields
        callback(dfFundamental, factorName, params)
        # print(dfFundamental.head(10))
        if export:
            dfFundamental.to_csv(outputFolder + "dfFundamental_" + factorName + "_" + symbol + ".csv")

        # ---Process infinity---
        dfFundamental.replace([np.inf, -np.inf], np.nan, inplace=True)

        # ---Loop DataFrame---
        minDate = Gadget.MinDateTime()
        for index, row in dfFundamental.iterrows():
            #
            value = row[factorName]
            period = row["Period"]
            reportDate = row["ReportDate"]
            releaseDate = row["ReleaseDate"]
            #
            localDateTime = releaseDate
            localDateTime = Gadget.ToDate(localDateTime) # No TimeZone

            # 避免报表中的无效ReleaseDate
            if localDateTime < minDate: # 000602.SZ 000618.SZ 000699.SZ 000763.SZ
                continue

            # stdDateTime = Gadget.ToUTCDateTime(localDateTime)

            # ---Only Calculate within Datetime1 to Datetime2---
            # ---ReleaseDate Based---
            if localDateTime < realDateTime1 or localDateTime > realDateTime2:
                continue

            # 只提示，不处理
            if math.isnan(value):
                # print(factorName + " is NaN " + symbol + " @ " + str(localDateTime))
                continue

            # if index == 0 and math.isnan(row[factorName]):
            #    continue

            # ---发布后最近的一次交易日---
            quote = bmQuotes.Get(localDateTime, "Next")
            if quote == None:
                # 发布日期超越了BM日期
                continue
            tradeDateTime = quote["DateTime"]
            utcDateTime = Gadget.ToUTCDateTime(tradeDateTime)

            # ---
            document = GenarateDocument(symbol, tradeDateTime, value)
            document["Period"] = period
            document["ReportDate"] = reportDate
            # document["ReportYear"] = reportDate.year
            document["Key"] = symbol + "_" + Gadget.ToDateTimeString(document["ReportDate"])

            # ---if surplus Field export needed---
            if len(outputField) != 0:
                for field in outputField:
                    document[field] = row[field]

            dataSeries.Add(document)

        # ---Write to Database---
        database.SaveDataSeries(dataSeries)
        pass


def CalcPriceFundamentalCombinedFactor(database, instruments, datetime1, datetime2,
                                       factorName, fields=[], quarterlyFields=[],
                                       callback=None, update=False, params={}, stratIndex=0):
    #
    outputFolder = "D:/Data/CalcValuationFactor/" + factorName + "/"
    Gadget.CreateFolder(outputFolder)

    # ---用于确定，交易日+交易时间，写入数据时间戳---
    bmQuotes = database.GetDataSeries("000001.SH",
                                      instrumentType="Index", dataType="DailyBar",
                                      datetime1=datetime1, datetime2=datetime2)

    #
    count = 0
    for instrument in instruments:
        symbol = instrument["Symbol"]
        count += 1
        print("")
        print("Process", symbol, count, instrument["DateTime2"])
        #
        if count < stratIndex:
            continue

        # ---Rebuild DateTime Range ---
        # datetime1=realDateTime1 ---> 指定的开始时间 或者 上次更新到的时间
        # datetime2=rangeEnd ---> price 只有这么多数据
        # ---确定起始日期---
        realDateTime1, realDateTime2 = DeterminFactorCalculatingRange(database, instrument, datetime1, datetime2,
                                                                     factorName, update)

        print("TimeRange", realDateTime1, realDateTime2)

        # ---Create DataSeries---
        dataSeries = DataSeries.DataSeries(symbol + "_" + factorName + "_Factor")

        # ---Load Price into DataFrame---
        dfPrice = IO.LoadBarsAsDataFrame(database, symbol, instrumentType="Stock",
                                         datetime1=realDateTime1, datetime2=realDateTime2, fields=["Close", "TotalShares"])
        # No data
        if dfPrice.shape[0] == 0:
            # return pd.DataFrame()
            print(symbol, "No Price Data")
            continue

        rangeBegin = dfPrice.loc[0, "DateTime"]
        realDateTime1 = rangeBegin

        rangeEnd = dfPrice.loc[len(dfPrice) - 1, "DateTime"]
        realDateTime2 = rangeEnd

        #
        # print(dfPrice)

        # ---Correct DateTime---
        # ---Convert String DateTime to Std DateTime---
        # for index in dfPrice.index:
        #    newDatetime = pd.to_datetime(dfPrice.at[index, "DateTime"])
        #    dfPrice.at[index, "DateTime"] = datetime.datetime(newDatetime.year, newDatetime.month, newDatetime.day)
        # dfPrice['DateTime'] = pd.to_datetime(dfPrice['DateTime'])
        # print(dfPrice.head(10))
        # print(dfPrice.info())

        # ---补齐与合并要提取的字段---
        totalFields = copy.deepcopy(fields)
        for field in quarterlyFields:
            if field not in totalFields:
                totalFields.append(field)

        # ---Load Fundamental into DataFrame---
        dfFundamental = IO.LoadFundamentalsAsDataFrame(database, symbol, datetime1=None, datetime2=realDateTime2, fields=totalFields)

        if dfFundamental.shape[0] == 0:
            # return pd.DataFrame()
            print(symbol, "No Fundamental Data")
            continue

        # ---防止取报表中的 Close 和 TotalShares 会和 Dailybar 中字段重名---
        rename = {}
        if "Close" in totalFields:
            rename["Close"] = "Close_Report"
        if "Close" in totalFields:
            rename["TotalShares"] = "TotalShares_Report"
        if len(rename) > 0:
            dfFundamental.rename(columns=rename, inplace=True)
            for k, v in rename.items():
                totalFields.remove(k)
                totalFields.append(v)

        # ---Calculate TTM / LYR---
        for field in totalFields:
            # ---LYR for All item---
            CalculateLYR2(database, dfFundamental, field)
        # print(dfFundamental.head(10))

        # ---TTM only for Quarterly item ---
        for field in quarterlyFields:
            CalculateTTM2(database, dfFundamental, field)
        # print(dfFundamental.head(10))

        # ---Merge 时， Release Date 可能不是交易日----
        # ---所以最后拼接出来的总日期有可能混入非交易日---
        # 统一DateTime，以便排序---
        dfFundamental["Date"] = dfFundamental["ReleaseDate"]
        # print(dfFundamental)
        # dfFundamental.to_csv(outputFolder + "dfFundamental_" + symbol + ".csv")
        #
        dfCombine = pd.merge(dfPrice, dfFundamental, on='Date', how='outer')
        dfCombine.sort_values(by=["Date","ReportDate"], inplace=True) # 防止同一天发布
        # print(dfFundamental)
        dfCombine.fillna(method='ffill', inplace=True)
        # print(dfCombine.head(10))
        # dfCombine.to_csv(outputFolder + "dfFundamental+Price_" + symbol + ".csv")

        # ---Calculation---
        # ---Calculation Cap Factor---
        dfCombine["Cap"] = dfCombine["Close"] * dfCombine["TotalShares"]

        # ---Calculation Logics---
        dfCombine = callback(dfCombine, factorName, params)
        dfCombine.to_csv(outputFolder + "dfCombine_" + symbol + ".csv")

        # --- Loop Dates (Beginning day of Month) ---
        # --- 按照 TradeDate Time 1500 来记录 factor value---
        datetimes = Gadget.GenerateMonthDates(realDateTime1, realDateTime2)
        for dt in datetimes:
            # ---寻找最近的交易日---
            bmBar = bmQuotes.Get(dt)
            if bmBar == None:
                continue
            tradeDateTime = bmBar["DateTime"]
            # print("MonthDate " + str(dt) + " TradeDate " + str(tradeDateTime))
            # localTradeDate = Gadget.ToDate(tradeDateTime)
            # localTradeDate = Gadget.ToLocalDateTime(tradeDateTime).date()

            #---Find Recent Trade Date Value---
            dfTemp = dfCombine[dfCombine['DateTime'] <= tradeDateTime]
            # print("Total " + str(len(dfCombine)) + " LessThenDate " + str(len(dfTemp)))

            if len(dfTemp) == 0:
                continue

            # dfTemp = dfTemp.iloc[-1,:]
            # print(dfTemp)
            # print(dfTemp["DateTime"])
            # --- Generate Document, Fill to DataSeries ---
            row = dfTemp.iloc[-1,:] # 最后一行
            value = row[factorName]

            # check valid
            if math.isnan(value) or math.isinf(value) or math.isinf(value):
                continue

            #
            document = GenarateDocument(symbol, tradeDateTime, value)
            document["Period"] = row["Period"]
            document["ReportDate"] = Gadget.ToDate(row["ReportDate"])
            # document["ReportYear"] = row["ReportDate"].year
            document["Key"] = symbol + "_" + Gadget.ToDateTimeString(tradeDateTime)
            dataSeries.Add(document)

        # ---Save to database---
        database.SaveDataSeries(dataSeries)
        pass


def CalcFactorOfFactor(database, instruments, datetime1, datetime2,
                       factorName, callback,):
    pass


def DivideCallback(dfFundamental, factorName, params):
    #
    type = "_" + params["Type"]
    numerator = params["Numerator"]
    denominator = params["Denominator"]
    #
    if "LYR" in type:
        dfFundamental[factorName] = dfFundamental[numerator + type] / dfFundamental[denominator + type]
    elif "TTM" in type:
        dfFundamental[factorName] = dfFundamental[numerator + type] / dfFundamental[denominator]


def ConsensusDivideCallback(dfFundamental, factorName, params):
    #
    if "Type" in params:
        type = "_" + params["Type"]
    else:
        type = ""
    #
    numerator = params["Numerator"]
    denominator = params["Denominator"]
    #
    dfFundamental[factorName] = dfFundamental[numerator + type] / dfFundamental[denominator + type]